package start.spring.basic.dynamic;

import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import start.spring.basic.exception.common.DbException;
import start.spring.basic.util.PageUtil;
import start.spring.basic.util.common.StringUtil;

/**
 * 
 * ****************************************************************************
 * 动态JdbcTemplate数据库查询操作类
 * 此类适用于“跨库”使用jdbc进行查询的场景，非查询操作或者且需要控制事务的场景通过获取原生的jdbc连接进行
 *
 * @author(作者)：xuyongyun	
 * @date(创建日期)：2021年6月24日
 ******************************************************************************
 */
@Repository
public class DynamicJdbc {
	
	@Autowired
	DynamicDSRegister dsRegister;
	
	/**
	 * 获取数据源
	 *	
	 * @param dsKey
	 * @return
	 * @since           1.0
	 */
	public DataSource getDs(String dsKey) {
		return getTemplate(dsKey).getDataSource();
	}

	private JdbcTemplate getTemplate(String dsKey) {
		if(StringUtil.isEmpty(dsKey)) {
			return dsRegister.getTemple(DsConstant.DEFAULT);
		}else {
			return dsRegister.getTemple(dsKey);
		}
	}
    
	/**
	 * 查询方法，返回List<Map<String, Object>>
	 * 
	 * @param sql 查询sql
	 * @param prams 查询参数，无参数时传入null即可
	 * 
	 * @return List<Map<String, Object>>
	 * 
	 * @throws DbException 自定义数据库异常
	 * 
	 * @since           1.0
	 */
	public List<Map<String, Object>> query(String dsKey,String sql,final Object[] prams) throws DbException{
    	List<Map<String, Object>> resultList = null;
    	try {
			if(prams==null||prams.length==0){
				resultList =  this.getTemplate(dsKey).queryForList(sql);
			}else{
				resultList = this.getTemplate(dsKey).queryForList(sql,prams);
			}
    	}catch(Exception e) {
    		this.handleException(e);
    	}
		
		return resultList;
    }
	
	/**
	 * 查询方法，返回Map<String, Object>
	 * 
	 * @param sql 查询sql
	 * @param prams 查询参数，无参数时传入null即可
	 * 
	 * @return Map<String, Object>
	 * 
	 * @throws DbException 自定义数据库异常
	 * 
	 * @since           1.0
	 */
	public Map<String, Object> getOne(String dsKey,String sql,final Object[] prams) throws DbException{
    	List<Map<String, Object>> resultList = query(dsKey,sql,prams);
		if(resultList!=null&&resultList.size()>0) {
			return resultList.get(0);
		}else {
			return null;
		}
    }
	
	/**
	 * 查询方法，返回指定的实体对象列表List<T>
	 * 
	 * @param sql 查询sql
	 * @param prams 查询参数，无参数时传入null即可
	 * 
	 * @return List<T>
	 * 
	 * @throws DbException 自定义数据库异常
	 * 
	 * @since           1.0
	 */
	public <T> List<T> query(String dsKey,String sql,final Object[] prams,final Class<T> clazz) throws DbException{
    	List<T> resultList = null;
    	try {
			if(prams==null||prams.length==0){
				resultList =  this.getTemplate(dsKey).query(sql, new BeanPropertyRowMapper<T>(clazz));
			}else{
				resultList = this.getTemplate(dsKey).query(sql, prams, new BeanPropertyRowMapper<T>(clazz));
			}
    	}catch(Exception e) {
    		this.handleException(e);
    	}
		
		return resultList;
    }
	
	/**
	 * 查询方法，返回一个指定的实体对象T
	 * 
	 * @param sql 查询sql
	 * @param prams 查询参数，无参数时传入null即可
	 * 
	 * @return T 需要返回的实体对象
	 * 
	 * @throws DbException 自定义数据库异常
	 * 
	 * @since           1.0
	 */
	public <T> T getOne(String dsKey,String sql,final Object[] prams,final Class<T> clazz) throws DbException{
    	List<T> resultList = query(dsKey,sql,prams,clazz);
    	if(resultList!=null&&resultList.size()>0) {
			return resultList.get(0);
		}else {
			return null;
		}
    }
	
    /**
     * 分页查询，返回结果集类型List<Map<String,Object>>
     * 
     * @param sql 查询sql
     * @param prams 查询参数，无参数时传入null即可
     * 
     * @param page 分页对象
     * 
     * @param sqlCount 用于获取结果集总数，用于优化sql中带有order by时的效率优化，如果不传入，则使用传入的sql统计结果集总数
     * 
     * @return PageUtil<Map<String,Object>>
     * 
     * @throws DbException
     * @since           1.0
     */
	public PageUtil<Map<String,Object>> queryByPageMysql(String dsKey,String sql,Object[] prams, 
			PageUtil<Map<String,Object>> page, String sqlCount) throws DbException {

    	try {
			
			if(prams==null) {
				prams = new Object[]{};
			}
			
			long totalCount = page.getTotalRecords();
			long currPage = page.getCurrentPage();
			
			//查询第一页或者设置了必须查询数据总量
			if(currPage==1||page.getIsQueryCount()) {
				String countSql = sqlCount;
				if(StringUtil.isEmpty(sqlCount)) {
					countSql = sql;
				}
				
				int fromIndex = countSql.indexOf("FROM");
				if(fromIndex < 0) {
					fromIndex = countSql.indexOf("from");
				}
				countSql = countSql.substring(fromIndex - 1);
				
				StringBuffer totalSQL = new StringBuffer();
				totalSQL.append("SELECT COUNT(*) ZS ");
				totalSQL.append(countSql);
				
				List<Map<String, Object>> countList = this.getTemplate(dsKey).queryForList(totalSQL.toString(),prams);
				if(countList!=null&&countList.size()==1) {
					Map<String, Object> tmpMap = countList.get(0);
					totalCount = Integer.parseInt(
						  String.valueOf(tmpMap.get("ZS")));
				}
				page.setTotalRecords(totalCount);
			}

			StringBuffer pageSQL = new StringBuffer();
			pageSQL.append(sql);
			pageSQL.append(" LIMIT ?,? ");
			
			Object[] pramsArray = new Object[prams.length + 2];
			System.arraycopy(prams, 0, pramsArray, 0, prams.length);
			pramsArray[prams.length] = (page.getCurrentPage() - 1)*page.getNumPerPage();
			pramsArray[prams.length + 1] = page.getNumPerPage();
		  
			page.setResultList(this.getTemplate(dsKey).queryForList(pageSQL.toString(),pramsArray));
			
		}catch(Exception e) {
			this.handleException(e);
    	}
		return page;
    }
	
	/**
     * 分页查询，返回结果集类型List<T>，T为自定义java对象
     * 
     * @param sql 查询sql
     * @param prams 查询参数，无参数时传入null即可
     * 
     * @param page 分页对象
     * 
     * @param sqlCount 用于获取结果集总数，用于优化sql中带有order by时的效率优化，如果不传入，则使用传入的sql统计结果集总数
     * 
     * @return PageUtil<T>
     * 
     * @throws DbException
     * @since           1.0
     */
	public <T> PageUtil<T> queryByPageMysql(String dsKey,String sql,Object[] prams, PageUtil<T> page, 
			String sqlCount,final Class<T> clazz) throws DbException {

    	try {
			
			if(prams==null) {
				prams = new Object[]{};
			}
			
			long totalCount = page.getTotalRecords();
			long currPage = page.getCurrentPage();
			
			//查询第一页或者设置了必须查询数据总量
			if(currPage==1||page.getIsQueryCount()) {
				String countSql = sqlCount;
				if(StringUtil.isEmpty(sqlCount)) {
					countSql = sql;
				}
				
				int fromIndex = countSql.indexOf("FROM");
				if(fromIndex < 0) {
					fromIndex = countSql.indexOf("from");
				}
				countSql = countSql.substring(fromIndex - 1);
				
				StringBuffer totalSQL = new StringBuffer();
				totalSQL.append("SELECT COUNT(*) ZS ");
				totalSQL.append(countSql);
				
				List<Map<String, Object>> countList = this.getTemplate(dsKey).queryForList(totalSQL.toString(),prams);
				if(countList!=null&&countList.size()==1) {
					Map<String, Object> tmpMap = countList.get(0);
					totalCount = Integer.parseInt(
						  String.valueOf(tmpMap.get("ZS")));
				}
				page.setTotalRecords(totalCount);
			}

			StringBuffer pageSQL = new StringBuffer();
			pageSQL.append(sql);
			pageSQL.append(" LIMIT ?,? ");
			
			Object[] pramsArray = new Object[prams.length + 2];
			System.arraycopy(prams, 0, pramsArray, 0, prams.length);
			pramsArray[prams.length] = (page.getCurrentPage() - 1)*page.getNumPerPage();
			pramsArray[prams.length + 1] = page.getNumPerPage();
			
			page.setResultList(this.getTemplate(dsKey).query(pageSQL.toString(),pramsArray, new BeanPropertyRowMapper<T>(clazz)));
			
		}catch(Exception e) {
			this.handleException(e);
    	}
		return page;
    }
	
	/**
     * 分页查询，返回结果集类型List<Map<String,Object>>
     * 
     * @param sql 查询sql
     * @param prams 查询参数，无参数时传入null即可
     * 
     * @param page 分页对象
     * 
     * @param sqlCount 用于获取结果集总数，用于优化sql中带有order by时的效率优化，如果不传入，则使用传入的sql统计结果集总数
     * 
     * @return PageUtil<Map<String,Object>>
     * 
     * @throws DbException
     * @since           1.0
     */
	public PageUtil<Map<String,Object>> queryByPageOracle(String dsKey,String sql,Object[] prams, PageUtil<Map<String,Object>> page, String sqlCount) throws DbException {

    	try {
			if(prams==null) {
				prams = new Object[]{};
			}
			
			long totalCount = page.getTotalRecords();
			long currPage = page.getCurrentPage();
			
			//查询第一页或者设置了必须查询数据总量
			if(currPage==1||page.getIsQueryCount()) {
				String countSql = sqlCount;
				if(StringUtil.isEmpty(sqlCount)) {
					countSql = sql;
				}
				
				int fromIndex = countSql.indexOf("FROM");
				if(fromIndex < 0) {
					fromIndex = countSql.indexOf("from");
				}
				countSql = countSql.substring(fromIndex - 1);
				
				StringBuffer totalSQL = new StringBuffer();
				totalSQL.append("SELECT COUNT(*) ZS ");
				totalSQL.append(countSql);
				
				List<Map<String, Object>> countList = this.getTemplate(dsKey).queryForList(totalSQL.toString(),prams);
				if(countList!=null&&countList.size()==1) {
					Map<String, Object> tmpMap = countList.get(0);
					totalCount = Integer.parseInt(
						  String.valueOf(tmpMap.get("ZS")));
				}
				page.setTotalRecords(totalCount);
			}

			StringBuffer pageSQL = new StringBuffer(" SELECT * FROM ( ");
			pageSQL.append(" SELECT temp.* ,ROWNUM num FROM ( ");
			pageSQL.append(sql);
			
			pageSQL.append(" ) temp where ROWNUM <= ? ");
			pageSQL.append(" ) WHERE num > ? ");
			
			Object[] pramsArray = new Object[prams.length + 2];
			System.arraycopy(prams, 0, pramsArray, 0, prams.length);
			pramsArray[prams.length] = page.getLastIndex();
			pramsArray[prams.length + 1] = page.getStartIndex();
			
			page.setResultList(this.getTemplate(dsKey).queryForList(pageSQL.toString(),pramsArray));
			
		}catch(Exception e) {
			this.handleException(e);
    	}
		return page;
    }
	
	/**
     * 分页查询，返回结果集类型List<T>，T为自定义java对象
     * 
     * @param sql 查询sql
     * @param prams 查询参数，无参数时传入null即可
     * 
     * @param page 分页对象
     * 
     * @param sqlCount 用于获取结果集总数，用于优化sql中带有order by时的效率优化，如果不传入，则使用传入的sql统计结果集总数
     * 
     * @return PageUtil<T>
     * 
     * @throws DbException
     * @since           1.0
     */
	public <T> PageUtil<T> queryByPageOracle(String dsKey,String sql,Object[] prams, PageUtil<T> page,
				String sqlCount,final Class<T> clazz) throws DbException {
    	try {
			if(prams==null) {
				prams = new Object[]{};
			}
			
			long totalCount = page.getTotalRecords();
			long currPage = page.getCurrentPage();
			
			//查询第一页或者设置了必须查询数据总量
			if(currPage==1||page.getIsQueryCount()) {
				String countSql = sqlCount;
				if(StringUtil.isEmpty(sqlCount)) {
					countSql = sql;
				}
				
				int fromIndex = countSql.indexOf("FROM");
				if(fromIndex<0) {
					fromIndex = countSql.indexOf("from");
				}
				countSql = countSql.substring(fromIndex - 1);
				
				StringBuffer totalSQL = new StringBuffer();
				totalSQL.append("SELECT COUNT(*) ZS ");
				totalSQL.append(countSql);
				
				List<Map<String, Object>> countList = this.getTemplate(dsKey).queryForList(totalSQL.toString(),prams);
				if(countList!=null&&countList.size()==1) {
					Map<String, Object> tmpMap = countList.get(0);
					totalCount = Integer.parseInt(
						  String.valueOf(tmpMap.get("ZS")));
				}
				page.setTotalRecords(totalCount);
			}

			StringBuffer pageSQL = new StringBuffer(" SELECT * FROM ( ");
			pageSQL.append(" SELECT temp.* ,ROWNUM num FROM ( ");
			pageSQL.append(sql);
			
			pageSQL.append(" ) temp where ROWNUM <= ? ");
			pageSQL.append(" ) WHERE num > ? ");
			
			Object[] pramsArray = new Object[prams.length + 2];
			System.arraycopy(prams, 0, pramsArray, 0, prams.length);
			pramsArray[prams.length] = page.getLastIndex();
			pramsArray[prams.length + 1] = page.getStartIndex();
			
			page.setResultList(this.getTemplate(dsKey).query(pageSQL.toString(),pramsArray, new BeanPropertyRowMapper<T>(clazz)));
			
		}catch(Exception e) {
			this.handleException(e);
    	}
		return page;
    }
	
	/**
	 * 公共db异常处理
	 *	
	 * @param e
	 * @since           1.0
	 */
	private void handleException(Exception e) {
		Throwable th = e.getCause();
		throw new DbException(e.toString(),th);
	}
	
}
